﻿#include "AppConfigDB.h"
#include <QSqlQuery>
#include <QSqlError>
#include <QDebug>
#include "SubTopicInfoEntity.h"

AppConfigDB::AppConfigDB(QObject *parent) : QObject(parent)
{
    if (!initDB())
    {
        qDebug() << "init db failed";
    }
}

AppConfigDB::~AppConfigDB()
{
    QSqlDatabase::database().close();
}

bool AppConfigDB::initDB()
{
    // open db
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName("app_config.db");
    if (!db.open())
    {
        qDebug() << "open db failed: " << db.lastError().text();
        return false;
    }

    // create table mqtt_info if not exists and set name as unique
    QSqlQuery query;
    if (!query.exec("create table if not exists mqtt_info (id integer primary key autoincrement, name text unique, host text, client_id text, user_name text, password text)"))
    {
        qDebug() << "create table mqtt_info failed: " << query.lastError().text();
        return false;
    }

    // create table sub_topic_info if not exists and set name as unique
    if (!query.exec("create table if not exists sub_topic_info (id integer primary key autoincrement, name text, topic text, qos integer, enable integer, hex integer)"))
    {
        qDebug() << "create table sub_topic_info failed: " << query.lastError().text();
        return false;
    }

    // create table pub_topic_info if not exists and set name as unique
    if (!query.exec("create table if not exists pub_topic_info (id integer primary key autoincrement, name text, topic text, qos integer, payload text, hex integer, description text)"))
    {
        qDebug() << "create table pub_topic_info failed: " << query.lastError().text();
        return false;
    }

    return true;
}

QList<MqttInfoEntity> AppConfigDB::getMqttInfoList()
{
    QList<MqttInfoEntity> list;
    QSqlQuery query;
    if (query.exec("select * from mqtt_info"))
    {
        while (query.next())
        {
            MqttInfoEntity entity;
            entity.setId(query.value("id").toInt());
            entity.setName(query.value("name").toString());
            entity.setHost(query.value("host").toString());
            entity.setClientId(query.value("client_id").toString());
            entity.setUserName(query.value("user_name").toString());
            entity.setPassword(query.value("password").toString());
            list.append(entity);
        }
    }
    else
    {
        qDebug() << "get mqtt info list failed: " << query.lastError().text();
    }
    return list;
}

bool AppConfigDB::addMqttInfo(const MqttInfoEntity &entity)
{
    QSqlQuery query;
    query.prepare("insert into mqtt_info (name, host, client_id, user_name, password) values (:name, :host, :client_id, :user_name, :password)");
    query.bindValue(":name", entity.name());
    query.bindValue(":host", entity.host());
    query.bindValue(":client_id", entity.clientId());
    query.bindValue(":user_name", entity.userName());
    query.bindValue(":password", entity.password());
    if (!query.exec())
    {
        qDebug() << "add mqtt info failed: " << query.lastError().text();
        return false;
    }
    return true;
}

bool AppConfigDB::updateMqttInfo(const MqttInfoEntity &entity)
{
    QSqlQuery query;
    query.prepare("update mqtt_info set name = :name, host = :host, client_id = :client_id, user_name = :user_name, password = :password where id = :id");
    query.bindValue(":name", entity.name());
    query.bindValue(":host", entity.host());
    query.bindValue(":client_id", entity.clientId());
    query.bindValue(":user_name", entity.userName());
    query.bindValue(":password", entity.password());
    query.bindValue(":id", entity.id());
    if (!query.exec())
    {
        qDebug() << "update mqtt info failed: " << query.lastError().text();
        return false;
    }
    return true;
}

bool AppConfigDB::addOrUpdateMqttInfoByName(const MqttInfoEntity &entity)
{
    QSqlQuery query;
    query.prepare("select * from mqtt_info where name = :name");
    query.bindValue(":name", entity.name());
    if (query.exec())
    {
        if (query.next())
        {
            // set id
            MqttInfoEntity newEntity = entity;
            newEntity.setId(query.value("id").toInt());
            return updateMqttInfo(newEntity);
        }
        else
        {
            return addMqttInfo(entity);
        }
    }
    else
    {
        qDebug() << "add or update mqtt info by name failed: " << query.lastError().text();
    }
    return false;
}

bool AppConfigDB::deleteMqttInfoByName(const QString &name)
{
    QSqlQuery query;
    query.prepare("delete from mqtt_info where name = :name");
    query.bindValue(":name", name);
    if (!query.exec())
    {
        qDebug() << "delete mqtt info by name failed: " << query.lastError().text();
        return false;
    }
    return true;
}

bool AppConfigDB::getMqttInfoByName(const QString &name, MqttInfoEntity &entity)
{
    QSqlQuery query;
    query.prepare("select * from mqtt_info where name = :name");
    query.bindValue(":name", name);
    if (query.exec())
    {
        if (query.next())
        {
            entity.setId(query.value("id").toInt());
            entity.setName(query.value("name").toString());
            entity.setHost(query.value("host").toString());
            entity.setClientId(query.value("client_id").toString());
            entity.setUserName(query.value("user_name").toString());
            entity.setPassword(query.value("password").toString());
            return true;
        }
    }
    else
    {
        qDebug() << "get mqtt info by name failed: " << query.lastError().text();
    }
    return false;
}

QList<SubTopicInfoEntity> AppConfigDB::getSubTopicInfoListByName(const QString &name)
{
    QList<SubTopicInfoEntity> list;
    QSqlQuery query;
    query.prepare("select * from sub_topic_info where name = :name");
    query.bindValue(":name", name);
    if (query.exec())
    {
        while (query.next())
        {
            SubTopicInfoEntity entity;
            entity.setId(query.value("id").toInt());
            entity.setName(query.value("name").toString());
            entity.setTopic(query.value("topic").toString());
            entity.setQos(query.value("qos").toInt());
            entity.setEnable(query.value("enable").toBool());
            entity.setHex(query.value("hex").toBool());
            list.append(entity);
        }
    }
    else
    {
        qDebug() << "get sub topic info list by name failed: " << query.lastError().text();
    }
    return list;
}

bool AppConfigDB::saveSubTopicInfoListByName(const QString &name, const QList<SubTopicInfoEntity> &list)
{
    QSqlQuery query;
    query.prepare("delete from sub_topic_info where name = :name");
    query.bindValue(":name", name);
    if (!query.exec())
    {
        qDebug() << "delete sub topic info by name failed: " << query.lastError().text();
        return false;
    }

    for (const SubTopicInfoEntity &entity : list)
    {
        query.prepare("insert into sub_topic_info (name, topic, qos, enable, hex) values (:name, :topic, :qos, :enable, :hex)");
        query.bindValue(":name", entity.name());
        query.bindValue(":topic", entity.topic());
        query.bindValue(":qos", entity.qos());
        query.bindValue(":enable", entity.enable());
        query.bindValue(":hex", entity.hex());
        if (!query.exec())
        {
            qDebug() << "save sub topic info by name failed: " << query.lastError().text();
            return false;
        }
    }
    return true;
}

bool AppConfigDB::modifySubTopicInfo(const SubTopicInfoEntity &entity)
{
    if (entity.id() < 0)
    {
        return false;
    }
    QSqlQuery query;
    query.prepare("update sub_topic_info set topic = :topic, qos = :qos, enable = :enable, hex = :hex where id = :id");
    query.bindValue(":topic", entity.topic());
    query.bindValue(":qos", entity.qos());
    query.bindValue(":enable", entity.enable());
    query.bindValue(":hex", entity.hex());
    query.bindValue(":id", entity.id());
    if (!query.exec())
    {
        qDebug() << "modify sub topic info failed: " << query.lastError().text();
        return false;
    }
    return true;
}

bool AppConfigDB::removeSubTopicInfo(const SubTopicInfoEntity &entity)
{
    if (entity.id() < 0)
    {
        return false;
    }
    QSqlQuery query;
    query.prepare("delete from sub_topic_info where id = :id");
    query.bindValue(":id", entity.id());
    if (!query.exec())
    {
        qDebug() << "remove sub topic info failed: " << query.lastError().text();
        return false;
    }
    return true;
}

bool AppConfigDB::addSubTopicInfo(const SubTopicInfoEntity &entity)
{
    QSqlQuery query;
    query.prepare("insert into sub_topic_info (name, topic, qos, enable, hex) values (:name, :topic, :qos, :enable, :hex)");
    query.bindValue(":name", entity.name());
    query.bindValue(":topic", entity.topic());
    query.bindValue(":qos", entity.qos());
    query.bindValue(":enable", entity.enable());
    query.bindValue(":hex", entity.hex());
    if (!query.exec())
    {
        qDebug() << "add sub topic info failed: " << query.lastError().text();
        return false;
    }
    return true;
}

bool AppConfigDB::addOrUpdateSubTopicInfo(const SubTopicInfoEntity &entity)
{
    if (entity.id() < 0)
    {
        return addSubTopicInfo(entity);
    }
    else
    {
        return modifySubTopicInfo(entity);
    }
}

QList<PubTopicInfoEntity> AppConfigDB::getPubTopicInfoListByName(const QString &name)
{
    QList<PubTopicInfoEntity> list;
    QSqlQuery query;
    query.prepare("select * from pub_topic_info where name = :name");
    query.bindValue(":name", name);
    if (query.exec())
    {
        while (query.next())
        {
            PubTopicInfoEntity entity;
            entity.setId(query.value("id").toInt());
            entity.setName(query.value("name").toString());
            entity.setTopic(query.value("topic").toString());
            entity.setQos(query.value("qos").toInt());
            entity.setPayload(query.value("payload").toString());
            entity.setHex(query.value("hex").toBool());
            entity.setDescription(query.value("description").toString());
            list.append(entity);
        }
    }
    else
    {
        qDebug() << "get pub topic info list by name failed: " << query.lastError().text();
    }
    return list;
}

bool AppConfigDB::savePubTopicInfoListByName(const QString &name, const QList<PubTopicInfoEntity> &list)
{
    QSqlQuery query;
    query.prepare("delete from pub_topic_info where name = :name");
    query.bindValue(":name", name);
    if (!query.exec())
    {
        qDebug() << "delete pub topic info by name failed: " << query.lastError().text();
        return false;
    }

    for (const PubTopicInfoEntity &entity : list)
    {
        query.prepare("insert into pub_topic_info (name, topic, qos, payload, hex, description) values (:name, :topic, :qos, :payload, :hex, :description)");
        query.bindValue(":name", entity.name());
        query.bindValue(":topic", entity.topic());
        query.bindValue(":qos", entity.qos());
        query.bindValue(":payload", entity.payload());
        query.bindValue(":hex", entity.hex());
        query.bindValue(":description", entity.description());
        if (!query.exec())
        {
            qDebug() << "save pub topic info by name failed: " << query.lastError().text();
            return false;
        }
    }
    return true;
}

bool AppConfigDB::modifyPubTopicInfo(const PubTopicInfoEntity &entity)
{
    if (entity.id() < 0)
    {
        return false;
    }
    QSqlQuery query;
    query.prepare("update pub_topic_info set topic = :topic, qos = :qos, payload = :payload, hex = :hex, description = :description where id = :id");
    query.bindValue(":topic", entity.topic());
    query.bindValue(":qos", entity.qos());
    query.bindValue(":payload", entity.payload());
    query.bindValue(":hex", entity.hex());
    query.bindValue(":description", entity.description());
    query.bindValue(":id", entity.id());
    if (!query.exec())
    {
        qDebug() << "modify pub topic info failed: " << query.lastError().text();
        return false;
    }
    return true;
}

bool AppConfigDB::removePubTopicInfo(const PubTopicInfoEntity &entity)
{
    if (entity.id() < 0)
    {
        return false;
    }
    QSqlQuery query;
    query.prepare("delete from pub_topic_info where id = :id");
    query.bindValue(":id", entity.id());
    if (!query.exec())
    {
        qDebug() << "remove pub topic info failed: " << query.lastError().text();
        return false;
    }
    return true;
}

bool AppConfigDB::addPubTopicInfo(PubTopicInfoEntity &entity)
{
    QSqlQuery query;
    query.prepare("insert into pub_topic_info (name, topic, qos, payload, hex, description) values (:name, :topic, :qos, :payload, :hex, :description)");
    query.bindValue(":name", entity.name());
    query.bindValue(":topic", entity.topic());
    query.bindValue(":qos", entity.qos());
    query.bindValue(":payload", entity.payload());
    query.bindValue(":hex", entity.hex());
    query.bindValue(":description", entity.description());
    if (!query.exec())
    {
        qDebug() << "add pub topic info failed: " << query.lastError().text();
        return false;
    }

    // get id
    query.prepare("select last_insert_rowid()");
    if (query.exec() && query.next())
    {
        entity.setId(query.value(0).toInt());
    }
    else
    {
        qDebug() << "get last insert row id failed: " << query.lastError().text();
    }
    return true;
}

bool AppConfigDB::addOrUpdatePubTopicInfo(PubTopicInfoEntity &entity)
{
    if (entity.id() < 0)
    {
        return addPubTopicInfo(entity);
    }
    else
    {
        return modifyPubTopicInfo(entity);
    }
}
